import datetime

from tool import util, dbhelper
from model import Video, Uploader
from manager import Manager
from tool import util
import config

def main():

    main_config = util.get_main_config()
    dbhelper.db_name = main_config["name"]
    dbhelper.db_host = main_config["host"]
    dbhelper.db_port = main_config["port"]
    dbhelper.db_user = main_config["user"]
    dbhelper.db_password = main_config["pwd"]
    print(main_config)

    success = dbhelper.createDB()
    if success == False:
        return False

    # util.set_main_config(None)

    creator = """CREATE TABLE IF NOT EXISTS `creator`(
        `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `name` varchar(100),
        `creator_id` varchar(50),
        `platform` varchar(20),
        `time` timestamp,
        `update_time` timestamp,
        `creator_desc` varchar(255) collate utf8mb4_unicode_ci
        )"""

    video = """CREATE TABLE IF NOT EXISTS `video` (
        `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `vid` varchar(20),
        `name` varchar(100),
        `url` varchar(255),
        `path` varchar(255),
        `img` varchar(255),
        `real_url` text,
        `create_time` timestamp,
        `creator_id` varchar(50),
        `platform` varchar(20),
        `title` varchar(100),
        `tags` varchar(255),
        `duration` varchar(255),
        `add_time` timestamp,
        `update_time` timestamp,
        `state` int default '0'
        )"""

    uploader = """CREATE TABLE IF NOT EXISTS `uploader` (
        `id` int NOT NULL AUTO_INCREMENT,
        `account` varchar(50) DEFAULT NULL COMMENT '账号',
        `pwd` varchar(50) DEFAULT NULL COMMENT '密码',
        `platform_id` int DEFAULT NULL COMMENT '所属平台',
        `selected` int DEFAULT '0' COMMENT '选中为1',
        `status` int DEFAULT '0' COMMENT '状态',
        `number` int DEFAULT NULL COMMENT '最大上传数量',
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;"""

    platform = """CREATE TABLE IF NOT EXISTS platform (
                id int auto_increment primary key,
                name varchar(50),
                abbr varchar(50),
                type int(1)
                )"""

    upload_video_log = """CREATE TABLE IF NOT EXISTS upload_video_log (
                        id int auto_increment primary key,
                        video_id int,
                        platform_id int,
                        uploader_id int,
                        time timestamp
                        )"""

    db = dbhelper.database()
    db.createTable('creator', creator)
    db.createTable('video', video)
    db.createTable('uploader', uploader)
    db.createTable('platform', platform)
    db.createTable('upload_video_log', upload_video_log)

    insertPlatform('快看点', 'kuaikandian', '2')
    insertPlatform('腾讯视频', 'tx', '1')

    return True

################################ Uploader ################################

def fetchUploaders():
    db = dbhelper.database()
    sql = 'SELECT * from uploader LEFT JOIN platform ON uploader.platform_id = platform.id'
    list = db.fetch(sql)
    data = []
    for row in list:
        uploader = Uploader()
        uploader.id = row[0]
        uploader.account = row[1]
        uploader.pwd = row[2]
        uploader.platform_id = row[3]
        uploader.selected = row[4]
        uploader.status = row[5]
        uploader.number = row[6]
        uploader.platform_name = row[8]
        uploader.uploaded_num = fetchVideoLogNum(uploader.id, 0)
        data.append(uploader)
    return data

def insertUploader(account, pwd, platform_id, number, id=None):
    db = dbhelper.database()
    params = {"account": account, "pwd": pwd,
              "platform_id": platform_id, "number": number}
    if id is None:
        db.commonInsert("uploader", params)
    else:
        db.commonUpdate("uploader", id, params)

def updateUploader(id, data):
    db = dbhelper.database()
    db.commonUpdate("uploader", id, data)

################################ Uploader ################################


################################ Platform ################################

# 0-通用，1-浏览 2-上传
def fetchPlatform(type=None):
    db = dbhelper.database()
    params = None
    if type is not None:
        params = {"type": str(type)}
    list = db.commonFetch("platform", params)
    data = []
    for row in list:
        platform = {"id": row[0], "name": row[1], "abbr": row[2],
                    "type": row[3]}
        data.append(platform)
    return data


def insertPlatform(name, abbr, type):
    db = dbhelper.database()
    isExist = db.commonExist("platform", {"abbr": abbr})
    if isExist:
        return
    params = {"name": name, "abbr": abbr, "type": str(type)}
    db.commonInsert('platform', params)


################################ Platform ################################


################################ Video ################################

def fetchVideoNumber(platform_id):
    db = dbhelper.database()
    return db.commonNumber("video", {"id": str(platform_id)})

def fetchTodayVideo(page=1, page_size=10, platform='tx'):
    if page <= 0:
        return []
    db = dbhelper.database()
    where_str = "where platform = '{}'".format(platform)
    where_str += " and datediff(add_time, now()) = '0'"
    sql_count = "SELECT COUNT(1) FROM video {}".format(where_str)
    ret_cout = db.fetch(sql_count)
    count = ret_cout[0][0]

    sql = "SELECT * from video {} order by create_time desc limit {},{}".format(
        where_str, (page-1)*page_size, page_size)
    list = db.fetch(sql)
    data = handleVideos(list)

    return data, count
    
def fetchVideo(page=1, page_size=10, platform='tx', keyword=None):
    if page <= 0:
        return []
    db = dbhelper.database()
    where_str = "where platform = '{}'".format(platform)
    if keyword is not None and len(keyword) > 0:
        # 关联的词
        arr = util.relation_keyword(keyword)
        keyword_str = ""
        for i in range(len(arr)):
            a = arr[i]
            if i == 0:
                keyword_str = " and name like '%{}%'".format(a)
            else:
                keyword_str += " or name like '%{}%'".format(a)
        where_str += keyword_str

    sql_count = "SELECT COUNT(1) FROM video {}".format(where_str)
    ret_cout = db.fetch(sql_count)
    count = ret_cout[0][0]

    sql = "SELECT * from video {} order by create_time desc limit {},{}".format(
        where_str, (page-1)*page_size, page_size)
    list = db.fetch(sql)
    data = handleVideos(list)
    return data, count

# 选中的视频
def fetchSelectedVideos(platform='tx'):
    sql = "SELECT * from video where platform = '{}' and state = '{}'".format(platform, "1")
    db = dbhelper.database()
    list = db.fetch(sql)
    data = handleVideos(list)
    return data


# 处理视频列表
def handleVideos(list):
    data = []
    for row in list:
        # print(row)
        video = Video()
        video.id = row[0]
        video.vid = row[1]
        video.name = row[2]
        video.url = row[3]
        video.path = row[4]
        video.img = row[5]
        video.real_url = row[6]
        video.create_time = row[7]
        video.creator_id = row[8]
        video.platform = row[9]
        video.title = row[10]
        video.tags = row[11]
        video.duration = row[12]

        video.is_uploaded = isUploadVideo(
            video.id, Manager.currentUploador.platform_id)

        if video.creator_id is not None:
            creators = fetchCreator({"creator_id": video.creator_id})
            if len(creators) > 0:
                video.creator_nick = creators[0][1]

        # vid
        if video.vid is None:
            vid = video.url.split('/')[-1]
            video.vid = vid.replace('.html', '')

        if video.img is None:
            if video.path is not None:
                video.img = video.path

        # 标题为空取name
        if video.title == None or len(video.title) == 0:
            video.title = video.name

        # 标签为空使用结巴分词，标题分词为字符串
        if video.tags == None or len(video.tags) == 0:
            video.tags = util.participle_str(video.title)

        data.append(video)
    return data

def insertVideo(data):
    db = dbhelper.database()
    vid = data['vid']
    params = {"vid": vid}
    existData = db.commonExistData("video", params)
    dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    if existData:
        data["update_time"] = dt
        db.commonUpdate("video", existData[0], data)
    else:
        data["add_time"] = dt
        db.commonInsert("video", data)


def updateVideo(id, data):
    db = dbhelper.database()
    db.commonUpdate("video", id, data)


# 记录
def isUploadVideo(video_id, platform_id):
    db = dbhelper.database()
    params = {"video_id": video_id, "platform_id": platform_id}
    return db.commonExist("upload_video_log", params)


# 今天day = 0 (与今天相差天数)
def fetchVideoLogNum(uploader_id, day=0):
    db = dbhelper.database()
    params = {"uploader_id": uploader_id, "datediff(time, now())": str(day)}
    return db.commonNumber("upload_video_log", params)


def insertVideoLog(video_id, platform_id, uploader_id):
    dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    params = {
        "video_id": video_id,
        "platform_id": platform_id,
        "uploader_id": uploader_id,
        "time": dt
    }
    db = dbhelper.database()
    db.commonInsert("upload_video_log", params)

################################ Video ################################


################################ Creator ################################

def fetchCreator(params=None):
    db = dbhelper.database()
    list = db.commonFetch("creator", params)
    return list


def insertCreator(creator_id, platform="tx", creator_nick=None, creator_desc=None):
    data = {"creator_id": creator_id}
    db = dbhelper.database()
    list = db.commonFetch("creator", data)
    if creator_nick != None:
        data["name"] = creator_nick
    if creator_desc != None:
        data["creator_desc"] = creator_desc
    dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    if util.isNotEmpty(list):
        # 更新
        data["update_time"] = dt
        db.commonUpdate("creator", list[0][0], data)
        return

    data["platform"] = platform
    data["time"] = dt
    data["update_time"] = dt
    db.commonInsert("creator", data)

################################ Creator ################################
